import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import processing.core.PApplet;
import processing.core.PFont;
import processing.core.PImage;


public class TimeZoneMap extends PApplet implements TLModule{

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	WorldMap wmap;
	LocationManager manager;
	private static int BEGIN_YEAR = 2010;
	private static int BEGIN_WEEK = 1;
	private static Week BEGIN_DAY = Week.MONDAY;
	private static int END_YEAR = 2010;
	private static int END_WEEK = 3;
	private static Week END_DAY = Week.MONDAY;
	private Map<String, TimeZone> timeZones;
	private Connection connection;
	private List<String> users;
	private List<Long> phraseIds;
	private Timestamp begin;
	private Timestamp end;
	private List<Long> songIds;
	private boolean dataChange;
	private static int MIN_ELLIPSE_SIZE = 10;
	private static long MAX_ELLIPSE_SIZE = 150;
	private PFont font;
	private static int CHART_ENTRIES = 100;
	private double scaling = 0.5;
	private boolean phrases;

	enum Week {MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY};
	public TimeZoneMap(){
		super();
		this.scaling = 1;
	}
	
	public TimeZoneMap(double scaling){
		super();
		this.scaling = scaling;
	}



	public void setup() {
		songIds = new ArrayList<Long>();
		songIds.add(1491L);
		begin = new Timestamp(1271714400000L);
		end = new Timestamp(1272664800000L);
		loadDatabase();
		initData();
		background(255);
		int width = (int)(1000D * scaling);
		int height = (int)(600D * scaling);
		size(width, height);
		ellipseMode(CENTER);
		smooth();

		wmap = new WorldMap();
		wmap.drawBackground();
		manager = new LocationManager(wmap);
		dataChange = true;
		font = createFont("calibri", 20);
		textFont(font, (float) (scaling * 30));
		textAlign(CENTER);

		/*manager.add(48.45, 2.35, 14, 100, 255); // Paris
		manager.add( -34.60, - 58.38, 14, 100, 255); // Buenos Aires
		manager.add( 17.96,  102.62, 14, 100, 255); // Vientiane
		manager.add( 52.31,  13.25, 14, 100, 255); // Berlin
		 */

	}

	private void initData() {
		songIds = new ArrayList<Long>();
		songIds.add(1491L);
		begin = new Timestamp(1271714400000L);
		end = new Timestamp(1272664800000L);
		users = new ArrayList<String>();
		phraseIds = new ArrayList<Long>();
		phrases = false;

	}

	private void loadDatabase() {
		connection = null;
		try {
			// Load the JDBC driver
			String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver
			Class.forName(driverName);

			// Create a connection to the database
			//String serverName = "djura.crabdance.com";
			String serverName = "localhost";
			String mydatabase = "infviz";
			String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url
			String username = "root";
			String password = "augmented";
			connection = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			// Could not find the database driver
		} catch (SQLException e) {
			// Could not connect to the database
		} 

	}

	public void draw() {
		if(dataChange){
			background(255);
			wmap.drawBackground();
			retrieveData();
			dataChange = false;
			manager.draw();
		}
		

	}

	private void retrieveData() {
		System.out.println("Retrieving data...");
		if(songIds.isEmpty() && phraseIds.isEmpty()){
			
		}else{
			String sql;
			Object[] params;
			if(phrases){
				sql = "SELECT timezones.*, COUNT(DISTINCT t3.tweet_id) nr_tweets FROM timezones, (SELECT t2.*, time_zone FROM user, (SELECT t1.*, from_user_id FROM tweet JOIN (SELECT phrase_tweet.phrase_id, phrase_tweet.tweet_id FROM phrase_tweet WHERE phrase_id = ?) t1 USING (tweet_id) WHERE created_date BETWEEN ? AND ?) t2 WHERE t2.from_user_id = user_id) t3 where time_zone = timezonename AND timezoneSize = 's' GROUP BY timezonename;";
				params = new Object[]{phraseIds.get(0), begin, end};
			}else{
				sql = "SELECT timezones.*, COUNT(DISTINCT t3.tweet_id) nr_tweets FROM timezones, (SELECT t2.*, time_zone FROM user, (SELECT t1.*, from_user_id FROM tweet JOIN (SELECT song_tweet.song_title, song_tweet.tweet_id FROM song_tweet, song WHERE song_tweet.song_title = song.song_title AND song.song_id = ?) t1 USING (tweet_id) WHERE created_date BETWEEN ? AND ?) t2 WHERE t2.from_user_id = user_id) t3 where time_zone = timezonename AND timezoneSize = 's' GROUP BY timezonename;";
				params = new Object[]{songIds.get(0), begin, end};
			}


			//String restrictionsSql = "SELECT MIN(timezoneCount) as min, MAX(timezoneCount) as max FROM testTable tt JOIN timezones tz on tt.timezoneId = tz.timezoneId";

			QueryRunner qr = new QueryRunner();
			ResultSetHandler rsh = new MapHandler();

			try {
				//			HashMap<String, Integer> restrictionsResult = (HashMap<String, Integer>) qr.query(connection, restrictionsSql, rsh);
				//			
				//			Integer min = restrictionsResult.get("min");
				//			System.out.println("Min: " + min);
				//			Integer max = restrictionsResult.get("max");
				//			System.out.println("Max: " + max);
				//			Integer diff = max - min;


				//String sql = "SELECT timezones.*, COUNT(DISTINCT t3.tweet_id) nr_tweets FROM timezones, (SELECT t2.*, time_zone FROM user, (SELECT t1.*, from_user_id FROM tweet JOIN (SELECT song_tweet.song_title, song_tweet.tweet_id FROM song_tweet, song WHERE song_tweet.song_title = song.song_title AND song.song_id = ?) t1 USING (tweet_id) WHERE created_date BETWEEN ? AND ?) t2 WHERE t2.from_user_id = user_id) t3 where time_zone = timezonename AND timezoneSize = 's' GROUP BY timezonename;";
				//Object[] params = new Object[]{new Long(1491), "2010-4-20", "2010-5-1"};

				rsh = new MapListHandler();
				System.out.println("Starting retrieving data...");
				long time = System.currentTimeMillis();
				List<Map<String, Object>> result = (List<Map<String,Object>>)qr.query(connection, sql, params, rsh);
				System.out.println("Data retrieved in: " + (System.currentTimeMillis() - time) + " ms");


				convertData(result);


			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}


	}

	/**
	 * Converts the maplist retrieved from the database to actual points in the map
	 * 
	 * @param result a list of maps with the following keys: 
	 */
	private void convertData(List<Map<String, Object>> result){

		long[] extremes = getExtremes(result);
		long min = extremes[0];
		long max = extremes[1];
		long diff = extremes[2];

		double normalizedDiff = MAX_ELLIPSE_SIZE - MIN_ELLIPSE_SIZE;
		int normalizedTwitterCount;
		long count;

		for(Map map : result){
			count = (Long) map.get("nr_tweets");
			double translatedCount = count - min;
			double dividedCount = translatedCount/(double)max * normalizedDiff;
			System.out.println("DividedCount: "+ dividedCount);
			normalizedTwitterCount = (int)(dividedCount + MIN_ELLIPSE_SIZE);

			//			System.out.println("NormalizedTwitterCount: " + normalizedTwitterCount);
			manager.add((Double)map.get("timezoneLatitude"), (Double)map.get("timezoneLongitude"), ((Long)map.get("nr_tweets")), normalizedTwitterCount, max, (String)map.get("timezoneName"));

		}


	}

	private long[] getExtremes(List<Map<String, Object>> result) {

		Map<String, Object> map = result.get(0);
		long min = (Long) map.get("nr_tweets");
		long max = min;
		long count;

		for(int i=0 ; i < result.size() ; i++){
			map = result.get(i);
			count = (Long) map.get("nr_tweets");
			if(count < min){
				min = count;
			}
			if(count > max){
				max = count;
			}

		}

		long difference = max - min;

		long[] extremes = {min, max, difference};

		return extremes;

	}

	class WorldMap {

		int x, y, w, h;
		PImage raster;

		WorldMap() {
			this(0, 0, width, height);
		}

		WorldMap(int x, int y, int w, int h) {
			if (h >= w/2) {
				this.w = w;
				this.h = w/2;
				this.x = x;
				this.y = (h - this.h)/2;
			} else {
				this.h = h;
				this.w = 2*h;
				this.x = (w - this.w)/2;
				this.y = y;
			}
			//this.w = (int)((double)(this.w) * scaling);
			//this.h = (int)((double)(this.h) * scaling);
			raster = loadImage("world_longlatwgs84.png");
		}

		void drawBackground() {
			image(raster, x, y, w, h);
		}

		Point getPoint(double phi, double lambda) {
			return new Point(
					x + ((180+lambda)/360)*w,
					y + h - ((90+phi)/180)*h
			);
		}

	}

	class Point extends Point2D {
		Point(double x, double y) { super(x, y); }
	}

	class Point2D {
		double x, y;
		Point2D(double x, double y) {
			this.x = x; this.y = y;
		}
	} 

	class LocationManager{
		HashMap<String, Location> locations; 
		WorldMap map;
		boolean change;

		public LocationManager(WorldMap map){
			locations = new HashMap<String,Location>();
			this.map = map;
			change = false;
		}

		public void add(double latitude, double longitude, int size, long count, int rColor, int gColor, int bColor, int alpha, String name){
			Location loc = new Location(map, latitude, longitude, size, count, rColor, gColor, bColor, alpha, name);

			locations.put(loc.getName(), loc);
			change = true;

		}

		public void add(double latitude, double longitude, long twitterCount, int normalizedTwitterCount, long maxCount, String name){
			int size = normalizedTwitterCount;
			int red = (int)((double)twitterCount / (double)CHART_ENTRIES * 255);
			int blue = 255 - red;	
			int green = (int) ((double)blue/255D * 148);

			add(latitude, longitude, size, twitterCount, red, green, blue, 200, name);

		}

		public void reset(){
			locations = new HashMap<String,Location>();
			TimeZoneMap.this.wmap.drawBackground();
		}


		public void draw(){
			if(change){

				Set<String> keys = locations.keySet();
				Location loc;

				for(String key : keys){
					loc = locations.get(key);					
					loc.draw();

				}
				change = false;
			}
		}


	}

	class Location{
		Point point;
		int alpha;
		int size;
		String name;
		private int rColor;
		private int gColor;
		private int bColor;
		private long count;


		public Location(WorldMap map, double latitude, double longitude, int size, long count, int rColor, int gColor, int bColor, int alpha, String name){
			point = map.getPoint(latitude, longitude);
			this.size = size;
			this.alpha = alpha;
			this.rColor = rColor;
			this.gColor = gColor;
			this.bColor = bColor;
			this.count = count;
			this.name = name;

		}

		public void draw(){
			noStroke();
			TimeZoneMap.this.fill(rColor, gColor, bColor, alpha);
			//			System.out.println("size: " + size);

			float x = (float) (point.x * scaling);
			float y = (float) (point.y * scaling);
			float scaledSize = (float) (size * scaling);

			TimeZoneMap.this.ellipse((float)point.x, (float)point.y -5 , scaledSize, scaledSize);
			TimeZoneMap.this.fill(255);
			TimeZoneMap.this.text(String.valueOf(count), (float)point.x, (float)point.y);
			//			TimeZoneMap.this.fill(0);
			//			TimeZoneMap.this.text(name, (float)point.x, (float)point.y + 20);
			TimeZoneMap.this.noFill();
		}

		public String getName(){
			return name;
		}

	}


	public Map<TimeZone, Integer> countTweets(int beginYear, int beginWeek, int beginDay, int endYear, int endWeek, int endDay){

		ResultSetHandler rsh = new MapListHandler();
		QueryRunner qr = new QueryRunner();
		String sql = "SELECT * FROM user WHERE time_zone = \"Amsterdam\" ";
		List<Map<String, Object>> result = null;
		try {
			result = (List<Map<String, Object>>) qr.query(connection, "SELECT * FROM user WHERE time_zone = \"Amsterdam\" ", rsh);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;		
	}


	class TimeZone{

		double latitude;
		double longitude;
		boolean bigArea;

		public TimeZone(double latitude, double longitude, String bigAreaString){
			if(bigAreaString.equals("b")){
				this.bigArea = true;
			}else{
				this.bigArea = false;
			}

			this.latitude = latitude;
			this.longitude = longitude;
		}

		public boolean isBigArea() {
			return bigArea;
		}

		public void setBigArea(boolean bigArea) {
			this.bigArea = bigArea;
		}

		public double getLatitude() {
			return latitude;
		}

		public void setLatitude(double latitude) {
			this.latitude = latitude;
		}
		public double getLongitude() {
			return longitude;
		}

		public void setLongitude(double longitude) {
			this.longitude = longitude;
		}

		public String toString(){
			return "Latitude: " + getLatitude() + " Longitude: " + getLongitude() + " bigArea: " + isBigArea(); 
		}


	}

	@Override
	public void highlightPhrase(List<Long> phraseId) {
		// TODO Auto-generated method stub

	}

	@Override
	public void highlightSong(List<Long> songId) {
		// TODO Auto-generated method stub

	}

	@Override
	public void highlightUser(List<String> users) {
		// TODO Auto-generated method stub

	}

	@Override
	public void reset() {
		resetPhrase();
		resetSong();
		resetTime();
		resetUser();

	}

	@Override
	public void resetPhrase() {
		phraseIds = new ArrayList<Long>();
		dataChange = true;
		phrases = false;


	}

	@Override
	public void resetSong() {
		songIds = new ArrayList<Long>();
		dataChange = true;

	}

	@Override
	public void resetTime() {
		begin = new Timestamp(0);
		end = new Timestamp(Long.MAX_VALUE);	
		dataChange = true;
	}

	@Override
	public void resetUser() {
		users = new ArrayList<String>();		
		dataChange = true;
	}

	@Override
	public void setPhrase(List<Long> phraseId) {
		phraseIds.addAll(phraseId);
		dataChange = true;
		phrases = true;

	}

	@Override
	public void setSong(List<Long> songId) {
		songIds.addAll(songId);
		dataChange = true;
		phrases = false;
	}

	@Override
	public void setTimeSpan(Timestamp begin	, Timestamp end) {
		this.begin = begin;
		this.end = end;
		dataChange = true;

	}

	@Override
	public void setUser(List<String> users) {
		this.users.addAll(users);
		dataChange = true;

	}

	@Override
	public void resetBillBoard(String chartName) {
		// TODO Auto-generated method stub

	}

	@Override
	public void setBillBoard(String chartName) {
		// TODO Auto-generated method stub

	}

}
